from pyecharts.charts import Bar, Page
import xlrd
from pyecharts.globals import ThemeType
from pyecharts import options as opts

def getValue(v):
    return round(v.value,2)

def render() -> Bar:
    # 创建条状图
    bar = Bar(init_opts={"width": "3000px"})
    bar.add_xaxis(["订单数（$）", "销售额（$）", "税费（$）", "联盟佣金（$）", "订单佣金（$）", "退款（$）",
                   "刷单（￥）", "销售额（￥）", "采购（￥）", "线上运费（￥）", "4PX运费（￥）", "通邮（￥）", "运费总计（￥）", "利润（￥）"])
    # 订单数	销售额	税费	联盟佣金	订单佣金 退款	刷单		销售额（人名币）	采购	线上运费	4PX运费	通邮	运费总计	利润
    # 读取excel数据
    data = xlrd.open_workbook("data.xlsx")
    sheet = data.sheet_by_index(4)
    nrows = sheet.nrows  # 获取表的行数
    for i in range(nrows):  # 循环逐行打印
        if i == 0:  # 跳过第一行
            continue
        row = sheet.row(i)
        bar.add_yaxis(row[0].value, [
                            getValue(row[1]),
                            getValue(row[2]),
                            getValue(row[3]),
                            getValue(row[4]),
                            getValue(row[5]),
                            getValue(row[6]),
                            getValue(row[7]),
                            getValue(row[8]),
                            getValue(row[9]),
                            getValue(row[10]),
                            getValue(row[11]),
                            getValue(row[12]),
                            getValue(row[13]),
                            getValue(row[14])
        ])
    # bar.add_yaxis("4月", [513, 9555.13, 559.57, 82.22, 306.07, 1762.91, 68.59, 42687.351, 17043.72, 7387.93, 656.61, 200,
    #                     8244.54, 17399.091])

    # render 会生成本地 HTML 文件，默认会在当前目录生成 render.html 文件
    # 也可以传入路径参数，如 bar.render("mycharts.html")
    bar.set_global_opts(title_opts=opts.TitleOpts(title="Bar-基本示例", subtitle="我是副标题"))
    return bar

# 显示平台相关数据
def showPlatformData(data) -> Bar:
    bar = Bar(init_opts=opts.InitOpts(chart_id="platform"))
    # bar.add_xaxis(["订单数（单）", "销售额（$）", "税费（$）", "联盟佣金（$）", "订单佣金（$）", "退款（$）"])
    bar.add_xaxis(["3月", "4月", "5月", "7月"])
    bar.add_yaxis("订单数（单）", data["order"])
    bar.add_yaxis("销售额（$）", data["sales"])
    bar.add_yaxis("税费（$）", data["taxes"])
    bar.add_yaxis("联盟佣金（$）", data["leagueComm"])
    bar.add_yaxis("订单佣金（$）", data["orderComm"])
    bar.add_yaxis("退款（$）", data["back"])

    bar.set_global_opts(title_opts=opts.TitleOpts(title="按月份统计平台数据情况", subtitle="妙妙头运动店"),
                        toolbox_opts=opts.ToolboxOpts(),
                        legend_opts=opts.LegendOpts(is_show=True)
                        )
    return bar

# 显示利润相关数据
def showProfitData(data) -> Bar:
    bar = Bar(init_opts=opts.InitOpts(chart_id="profit"))
    # 刷单（RMB）销售额（RMB）采购（RMB）线上运费（RMB）4PX运费（RMB）通邮（RMB）运费总计（RMB）利润（RMB）
    bar.add_xaxis(["3月", "4月", "5月", "7月"])

    bar.add_yaxis("销售额（RMB）", data["salesRmb"])
    bar.add_yaxis("刷单（RMB）", data["brush"])
    bar.add_yaxis("采购（RMB）", data["stock"])
    bar.add_yaxis("线上运费（RMB", data["onlineShip"])
    bar.add_yaxis("4PX运费（RMB）", data["px4Ship"])
    bar.add_yaxis("通邮（RMB）", data["tyShip"])
    bar.add_yaxis("运费总计（RMB）", data["totalShip"])
    bar.add_yaxis("利润（RMB）", data["profit"])

    bar.set_global_opts(title_opts=opts.TitleOpts(title="按月份统计利润数据情况", subtitle="妙妙头运动店"),
                        toolbox_opts=opts.ToolboxOpts(),
                        legend_opts=opts.LegendOpts(is_show=True)
                        )
    return bar

def renderPage(data):
    page = Page(layout=Page.DraggablePageLayout)
    page.add(
        showPlatformData(data),
        showProfitData(data)
    )
    # 加载chart_id的配置
    Page.save_resize_html(
        source="render.html",
        cfg_file="chart_config.json",
        dest="render-final.html"
    )
    page.render()


def loadAndHandleData():
    result = {}
    data = xlrd.open_workbook("data.xlsx")
    sheet = data.sheet_by_index(4)
    rows = sheet.nrows  # 获取表的行数
    # 订单数	销售额	税费	联盟佣金	订单佣金 退款
    orderData = []
    salesData = []
    taxesData = []
    leagueCommData = []
    orderCommData = []
    backData = []
    # 刷单（RMB）销售额（RMB）采购（RMB）线上运费（RMB）4PX运费（RMB）通邮（RMB）运费总计（RMB）利润（RMB）
    brushData = []
    salesRmbData = []
    stockData = []
    onlineShipData = []
    px4ShipData = []
    tyShipData = []
    totalShipData = []
    profitData = []
    for i in range(rows):  # 循环逐行打印
        if i == 0:  # 跳过第一行
            continue
        row = sheet.row(i)
        # 平台相关data
        orderData.append([row[0].value, getValue(row[1])])
        salesData.append([row[0].value, getValue(row[2])])
        taxesData.append([row[0].value, getValue(row[3])])
        leagueCommData.append([row[0].value, getValue(row[4])])
        orderCommData.append([row[0].value, getValue(row[5])])
        backData.append([row[0].value, getValue(row[6])])
        # 相关 profit data
        brushData.append([row[0].value, getValue(row[7])])
        salesRmbData.append([row[0].value, getValue(row[8])])
        stockData.append([row[0].value, getValue(row[9])])
        onlineShipData.append([row[0].value, getValue(row[10])])
        px4ShipData.append([row[0].value, getValue(row[11])])
        tyShipData.append([row[0].value, getValue(row[12])])
        totalShipData.append([row[0].value, getValue(row[13])])
        profitData.append([row[0].value, getValue(row[14])])

    result["order"] = orderData
    result["sales"] = salesData
    result["taxes"] = taxesData
    result["leagueComm"] = leagueCommData
    result["orderComm"] = orderCommData
    result["back"] = backData

    result["brush"] = brushData
    result["salesRmb"] = salesRmbData
    result["stock"] = stockData
    result["onlineShip"] = onlineShipData
    result["px4Ship"] = px4ShipData
    result["tyShip"] = tyShipData
    result["totalShip"] = totalShipData
    result["profit"] = profitData

    return result


if __name__ == '__main__':
    # 加载数据并处理
    data = loadAndHandleData()
    renderPage(data)

